﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MarginTrading.Entity;
using System.Data;
using System.Data.OracleClient;
using MarginTrading.Common;

namespace MarginTrading.Services
{
    public static class ReportServices
    {
        public static DataTable GetPortfolioValuation1(string clientId)
        {
            DataTable retVal = new DataTable();
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SECURITY.SYMBOL AS STOCKCODE," +
                                   "NAME AS SHAREDESCRIPTION, " +
                                   "QUANTITY,MARKET_VALUE AS MKTPRICE, " +
                                   "(QUANTITY * MARKET_VALUE) AS MKTVALUE,MARGIN_PERCENTAGE AS ALLOWED," +
                                   "((QUANTITY * MARKET_VALUE * MARGIN_PERCENTAGE)/100) AS MTMKTVALUE " +
                                   "FROM PORTFOLIO INNER JOIN SECURITY " +
                                   "ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                   "WHERE PORTFOLIO.CODE = '" + clientId + "' ORDER BY PORTFOLIO.CODE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }


        public static DataTable GetStockExposure(String sAgent, String sAdvisor)
        {
            DataTable retVal = new DataTable();
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                if (sAdvisor == "All")
                {
                    cmd.CommandText = "select p.SYMBOL STOCKCODE, sum(p.QUANTITY) QUANTITY, " +
                        "(select name from security where symbol = p.SYMBOL) SHAREDESCRIPTION, " +
                        "(select market_value from security where symbol = p.SYMBOL) MKTPRICE " +
                        " from PORTFOLIO p, client c  where p.code = c.code  and c.agent = '" + sAgent +
                        "' and margin_client = 1 group by p.SYMBOL order by p.SYMBOL";
                }
                else
                {
                    cmd.CommandText = "select p.SYMBOL STOCKCODE, sum(p.QUANTITY) QUANTITY, " +
                        "(select name from security where symbol = p.SYMBOL) SHAREDESCRIPTION, " +
                        "(select market_value from security where symbol = p.SYMBOL) MKTPRICE " +
                        " from PORTFOLIO p, client c  where p.code = c.code  and c.agent = '" + sAgent +
                        "' and margin_client = 1  and c.advisor = '"+ sAdvisor+"' group by p.SYMBOL order by p.SYMBOL";

                }

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }
        
        public static DataTable LoanBalanceTotals()
        {
            DataTable retVal = new DataTable();
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select distinct to_char(l.created_date, 'yyyy-MM-dd') LOANACCOUNTDATE, (select sum(amount) from loan_account where created_date < (to_date(to_char(l.created_date, 'ddMMyyyy'), 'ddMMyyyy')+1)) LOAN_BALANCE from loan_account l order by LOANACCOUNTDATE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }


        public static DataTable LoanBalanceSum(DateTime DFrom)
        {
            DataTable retVal = new DataTable();
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select client_code CLIENTCODE, sum(amount) LOAN_BALANCE from loan_account where  " +
                    " created_date < (to_date('" + DFrom.ToString("MM/dd/yyyy") + "', 'MM/dd/yyyy')+1)" +
                    " group by client_code order by client_code";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }


        public static DataTable LoanBalance(String AgentCode, String SortBy)
        {
            DataTable retVal = new DataTable();
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                String MTPORT = "(SELECT SUM(P.QUANTITY * S.MARKET_VALUE * S.MARGIN_PERCENTAGE/100) FROM PORTFOLIO P INNER JOIN SECURITY S ON P.SYMBOL = S.SYMBOL WHERE P.CODE = C.CODE)";
                String LOANBAL = "(SELECT SUM(L.AMOUNT) FROM LOAN_ACCOUNT  L WHERE L.CLIENT_CODE = c.CODE)";
                String LOANCOUNT = "(SELECT COUNT(L.AMOUNT) FROM LOAN_ACCOUNT  L WHERE L.CLIENT_CODE = c.CODE)";

                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "select C.code as CLIENTCODE, C.INITIALS as INITIALS , C.SURNAME as CLIENT_NAME, C.MARGIN_LIMIT as FACILITY_APPROVED, " +
                    "(CASE WHEN " + MTPORT + ">0 THEN " + MTPORT + " ELSE 1 END) as MT_PORTFOLIO_VALUE, " +
                    "(CASE WHEN " + LOANCOUNT + " = 0 THEN 0 ELSE " + LOANBAL + " END) as LOAN_BALANCE, " +
                    " CAST(((CASE WHEN " + LOANCOUNT + " = 0 THEN 0 ELSE " + LOANBAL + " END)*100/" +
                    "(CASE WHEN "+MTPORT+">0 THEN "+MTPORT+" ELSE 1 END)"+
                    ")AS NUMBER(16,2)) as MARGIN " +
                    "from client C where C.agent = '"+AgentCode+"' order by "+SortBy;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }

        public static DataTable GetStockwiseHolding(String sAgent, String sAdvisor, String Stock)
        {
            DataTable retVal = new DataTable();
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                if (sAdvisor == "All")
                {
                    cmd.CommandText = "select c.CODE ACCNO, c.SURNAME ACCNAME, c.INITIALS, p.QUANTITY, " +
                        "(select sum(a.QUANTITY*s.MARKET_VALUE) " +
                        "from portfolio a, security s where a.code = c.CODE and a.SYMBOL = s.SYMBOL) PERCENTAGE, " +
                        "(select MARKET_VALUE from security where symbol = p.SYMBOL) MARKET_VALUE "+
                        "from client c, portfolio p "+
                        "where c.code = p.code and p.SYMBOL = '" +
                        Stock + "' and c.AGENT = '" + sAgent + "' order by p.QUANTITY DESC";
                }
                else
                {
                    cmd.CommandText = "select c.CODE ACCNO, c.SURNAME ACCNAME, p.QUANTITY, " +
                         "(select sum(a.QUANTITY*s.MARKET_VALUE) " +
                         "from portfolio a, security s where a.code = c.CODE and a.SYMBOL = s.SYMBOL) PERCENTAGE, " +
                         "(select MARKET_VALUE from security where symbol = p.SYMBOL) MARKET_VALUE " +
                         "from client c, portfolio p " +
                         "where c.code = p.code and p.SYMBOL = '" +
                         Stock + "' and c.AGENT = '" + sAgent + "' and c.advisor = '"+ sAdvisor+"' order by p.QUANTITY DESC";
                }

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }



        public static DataTable GetTransactionsCollectionDataTableByClientIdAndDateRange(string clientId, DateTime fromDate, DateTime toDate)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select trade_date as TRADEDATE, " +
                                        "BUY_SELL as TRTYPE, " +
                                        "TRAN_ID as REFERENCE, " +
                                        "QUANTITY as NUMBEROFSHARES, " +
                                        "PRICE, " +
                                        "AMOUNT, " +
                                        "COMPANY_SYMBOL as PARTICULARS, " +
                                        "'' as BALANCE, " +
                                        "SETTLEMENT_DATE as SETTLEMENTDATE, " +
                                        "(CASE WHEN BUY_SELL = 'B' THEN (AMOUNT) ELSE 0 END) as PURCHASETURNOVER, " +
                                        "(CASE WHEN BUY_SELL = 'S' THEN (AMOUNT) ELSE 0 END) as SALESTURNOVER " +
                                        //"(CASE WHEN BUY_SELL = 'B' THEN (AMOUNT) ELSE 0 END) as TOTALRECEIPTS, " +
                                        //"(CASE WHEN BUY_SELL = 'B' THEN (AMOUNT) ELSE 0 END) as TOTALPAYMENTS " +
                                        "FROM DAILY_TRANSACTIONS " +
                                        "where CLIENT_CODE = :v1 " +
                                        "and TRADE_DATE >= :v2 " +
                                        "and TRADE_DATE <= :v3 "+
                                        "UNION ALL " +
                                        "SELECT CREATED_DATE as TRADEDATE," +
                                        "ENTRY_TYPE as TRTYPE," +
                                        "REFERENCE_NO as REFERENCE," +
                                        "0 as NUMBEROFSHARES," +
                                        "0 as PRICE," +
                                        "AMOUNT," +
                                        "TRANSACTION_PARTICULARS as PARTICULARS," +
                                        "'' as BALANCE, " +
                                        "null as SETTLEMENTDATE," +
                                        "0 as PURCHASETURNOVER," +
                                        "0 as SALESTURNOVER " +
                                        "FROM LOAN_ACCOUNT " +
                                        "where CLIENT_CODE = :v1 " +
                                        "and CREATED_DATE >= :v2 " +
                                        "and CREATED_DATE <= :v3 and entry_type in ('PA','RC') order by TRADEDATE";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = fromDate;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = toDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetLoanAccountCollectionDataTableByClientIdAndDateRange(string clientId, DateTime fromDate, DateTime toDate)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CREATED_DATE as LOANACCOUNTDATE, " +
                                        "LOAN_ACCOUNT.ENTRY_TYPE as TRXTYPE, " +
                                        "TRANSACTION_PARTICULARS as TRANSACTIONPARTICULARS, " +
                                        "REFERENCE_NO as REFERENCENUMBER, " +
                                        //"BALANCE, " +
                                        "AMOUNT, " +
                                        "(CASE WHEN AMOUNT < 0 THEN (AMOUNT*-1) ELSE 0 END) as RECEIPTS, " +
                                        "(CASE WHEN AMOUNT > 0 THEN (AMOUNT) ELSE 0 END) as PAYMENTS " +
                                        "FROM LOAN_ACCOUNT " +
                                        "WHERE CLIENT_CODE = :v1 " +
                                        "AND CREATED_DATE >= :v2 " +
                                        "AND CREATED_DATE <= :v3 ORDER BY CREATED_DATE";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = fromDate;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = toDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetMarginTradingStatusByAdvisorAndAgent(string advisorId, string agentCode)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT " +
                                  "MS.CLIENT_NAME as CLIENTNAME," +
                                  "MS.CLIENT_CODE as CLIENTCODE,"+
                                  "MS.ADVISOR as ADVISOR, " +
                                  "MS.FACILITY_APPROVED as FACILLTYAPPROVED," +
                                  "MS.PORTFOLIO_VALUE as MTPORTFOLIOVALUE," +
                                  "MS.LOAN_BALANCE as LOFINBALANCE,"+
                                  "MS.NET_FUTURE_BAL as NETFETUREBAL,"+
                                  "MS.PENDING_INTEREST as PENDINGINTEREST " +
                                  "FROM MARGIN_STATUS MS WHERE MS.MARGIN_DATE = (SELECT MAX(MARGIN_DATE) FROM MARGIN_STATUS WHERE CLIENT_CODE = MS.CLIENT_CODE AND AGENT = MS.AGENT AND ADVISOR = MS.ADVISOR) " +
                                  "AND MS.ADVISOR = :v1 AND MS.AGENT = :v2 ORDER BY LOFINBALANCE DESC";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisorId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = agentCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetMarginTradingStatusByAdvisorAndAgent(string agentCode)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT " +
                                  "MS.CLIENT_NAME as CLIENTNAME," +
                                  "MS.CLIENT_CODE as CLIENTCODE," +
                                  "MS.ADVISOR as ADVISOR, " +
                                  "MS.FACILITY_APPROVED as FACILLTYAPPROVED," +
                                  "MS.PORTFOLIO_VALUE as MTPORTFOLIOVALUE," +
                                  "MS.LOAN_BALANCE as LOFINBALANCE," +
                                  "MS.NET_FUTURE_BAL as NETFETUREBAL," +
                                  "MS.PENDING_INTEREST as PENDINGINTEREST " +
                                  "FROM MARGIN_STATUS MS WHERE MS.MARGIN_DATE = (SELECT MAX(MARGIN_DATE) FROM MARGIN_STATUS WHERE CLIENT_CODE = MS.CLIENT_CODE AND AGENT = MS.AGENT AND ADVISOR = MS.ADVISOR) " +
                                  "AND MS.AGENT = :v1 ORDER BY LOFINBALANCE DESC";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agentCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetMarginTradingStatusByAdvisor(string advisorId, string agentCode)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CLIENT.INITIALS ||' '|| CLIENT.SURNAME as CLIENTNAME, "+
                                  "CLIENT.CODE as CLIENTCODE, " +
                                  "CLIENT.ADVISOR as ADVISOR, " +
                                  "MARGIN_LIMIT as FACILLTYAPPROVED, " +
                                  "(SELECT SUM(PORTFOLIO.QUANTITY * SECURITY.MARKET_VALUE * SECURITY.MARGIN_PERCENTAGE/100) FROM PORTFOLIO " +
                                  "INNER JOIN SECURITY ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                  "WHERE PORTFOLIO.CODE = CLIENT.CODE) as MTPORTFOLIOVALUE, " +
                                  "(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE) as LOFINBALANCE, " +
                                  "(SELECT (TOTAL_DUE - BALANCE)*-1 FROM SETTLEMENT_DETAILS WHERE (SETTLEMENT_DETAILS.CLIENT_CODE = CLIENT.CODE AND ADDED_DATE = (SELECT MAX(ADDED_DATE) FROM SETTLEMENT_DETAILS WHERE SETTLEMENT_DETAILS.CLIENT_CODE = CLIENT.CODE))) as NETFETUREBAL, " +
                                  "(SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE DAILY_INTEREST.CLIENT_CODE = CLIENT.CODE and DAILY_INTEREST.ACC_POSTED = 0) as PENDINGINTEREST " +
                                  //"(((LOFINBALANCE + PENDINGINTEREST-(NETFETUREBAL * -1)) / (MTPORTFOLIOVALUE))*100) as MARGIN " +
                                  "FROM CLIENT "+
                                  //"INNER JOIN PORTFOLIO "+
                                  //"ON CLIENT.CODE = PORTFOLIO.CODE " +
                                  "where ADVISOR = :v1 AND MARGIN_CLIENT = 1 AND CLIENT.AGENT = :v2 ORDER BY LOFINBALANCE DESC";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisorId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = agentCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetMarginTradingStatusByAdvisor(string agentCode)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CLIENT.INITIALS ||' '|| CLIENT.SURNAME as CLIENTNAME, " +
                                  "CLIENT.CODE as CLIENTCODE, " +
                                  "CLIENT.ADVISOR as ADVISOR, " +
                                  "MARGIN_LIMIT as FACILLTYAPPROVED, " +
                                  "(SELECT SUM(PORTFOLIO.QUANTITY * SECURITY.MARKET_VALUE * SECURITY.MARGIN_PERCENTAGE/100) FROM PORTFOLIO " +
                                  "INNER JOIN SECURITY ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                  "WHERE PORTFOLIO.CODE = CLIENT.CODE) as MTPORTFOLIOVALUE, " +
                                  "(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE) as LOFINBALANCE, " +
                                  "(SELECT (TOTAL_DUE - BALANCE)*-1 FROM SETTLEMENT_DETAILS WHERE (SETTLEMENT_DETAILS.CLIENT_CODE = CLIENT.CODE AND ADDED_DATE = (SELECT MAX(ADDED_DATE) FROM SETTLEMENT_DETAILS WHERE SETTLEMENT_DETAILS.CLIENT_CODE = CLIENT.CODE))) as NETFETUREBAL, " +
                                  "(SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE DAILY_INTEREST.CLIENT_CODE = CLIENT.CODE and DAILY_INTEREST.ACC_POSTED = 0) as PENDINGINTEREST " +
                    //"(((LOFINBALANCE + PENDINGINTEREST-(NETFETUREBAL * -1)) / (MTPORTFOLIOVALUE))*100) as MARGIN " +
                                  "FROM CLIENT WHERE MARGIN_CLIENT = 1 AND CLIENT.AGENT = :v1 ORDER BY LOFINBALANCE DESC";// +
                    //"INNER JOIN PORTFOLIO "+
                    //"ON CLIENT.CODE = PORTFOLIO.CODE " +
                    //              "where ADVISOR = :v1 ";

                //cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisorId;
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agentCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetMarginTradingStatus(string agentCode)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CLIENT.AGENT, ADVISOR, CLIENT.INITIALS ||' '|| CLIENT.SURNAME as CLIENTNAME, " +
                                  "CLIENT.CODE as CLIENTCODE, " +
                                  "MARGIN_LIMIT as FACILLTYAPPROVED, " +
                                  "(SELECT SUM(PORTFOLIO.QUANTITY * SECURITY.MARKET_VALUE * SECURITY.MARGIN_PERCENTAGE/100) FROM PORTFOLIO " +
                                  "INNER JOIN SECURITY ON PORTFOLIO.SYMBOL = SECURITY.SYMBOL " +
                                  "WHERE PORTFOLIO.CODE = CLIENT.CODE) as MTPORTFOLIOVALUE, " +
                                  "(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE LOAN_ACCOUNT.CLIENT_CODE = CLIENT.CODE) as LOFINBALANCE, " +
                                  "(SELECT (TOTAL_DUE - BALANCE)*-1 FROM SETTLEMENT_DETAILS WHERE (SETTLEMENT_DETAILS.CLIENT_CODE = CLIENT.CODE AND ADDED_DATE = (SELECT MAX(ADDED_DATE) FROM SETTLEMENT_DETAILS WHERE SETTLEMENT_DETAILS.CLIENT_CODE = CLIENT.CODE))) as NETFETUREBAL, " +
                                  "(SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE DAILY_INTEREST.CLIENT_CODE = CLIENT.CODE and DAILY_INTEREST.ACC_POSTED = 0) as PENDINGINTEREST, " +
                                  "CURRENT_DATE as MARGIN_DATE " +
                    //"(((LOFINBALANCE + PENDINGINTEREST-(NETFETUREBAL * -1)) / (MTPORTFOLIOVALUE))*100) as MARGIN " +
                                  "FROM CLIENT WHERE MARGIN_CLIENT = 1 AND CLIENT.AGENT = :v1";// +
                //"INNER JOIN PORTFOLIO "+
                //"ON CLIENT.CODE = PORTFOLIO.CODE " +
                //              "where ADVISOR = :v1 ";

                //cmd.Parameters.Add(":v1", OracleType.VarChar).Value = advisorId;
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agentCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetTop3Days(string agent, DateTime date)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT TO_CHAR(TRADE_DATE,'yyyy/mm/dd') FROM DAILY_TRANSACTIONS " + 
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE "+
                                  "where CLIENT.AGENT = :v1 and TRADE_DATE <= :v2 GROUP BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') " +
                                  "ORDER BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') DESC";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetTop3Days(DateTime date)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT TO_CHAR(TRADE_DATE,'yyyy/mm/dd') FROM DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "where TRADE_DATE <= :v1 GROUP BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') " +
                                  "ORDER BY TO_CHAR(TRADE_DATE,'yyyy/mm/dd') DESC";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DateTime GetSettlementDate(DateTime date)
        {
            DateTime retVal = DateTime.MinValue;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') as TRADE_DATE FROM DAILY_TRANSACTIONS " +
                                  "where TO_CHAR(TRADE_DATE,'mm/dd/yyyy') =  TO_CHAR(:v1,'mm/dd/yyyy') GROUP BY TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') ";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        DateTime.TryParse(dataReader["TRADE_DATE"].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetDebtorsFollowupByAgentAndDate(string agent, DateTime date1, DateTime date2, DateTime date3)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.AGENT || ' / ' ||CLIENT.INITIALS || ' ' || "+
                                  "CLIENT.SURNAME as CLIENTNAME, "+
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as BUYDATE1, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE1 " +
                                  "from DAILY_TRANSACTIONS "+
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE "+
                                  "WHERE CLIENT.AGENT = :v1 AND TO_CHAR(TRADE_DATE,'mm/dd/yyyy') IN (TO_CHAR(:v2,'mm/dd/yyyy'),TO_CHAR(:v3,'mm/dd/yyyy'),TO_CHAR(:v4,'mm/dd/yyyy'))" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.OTHER_NAMES,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = date2;
                cmd.Parameters.Add(":v4", OracleType.DateTime).Value = date3;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetDebtorsFollowupByAgentAndDate(DateTime date1, DateTime date2, DateTime date3)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.AGENT || ' / ' ||CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'B'  THEN AMOUNT ELSE 0 END) as BUYDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as BUYDATE1, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v4,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE3, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v3,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE2, " +
                                  "sum(CASE WHEN TO_CHAR(TRADE_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as SELLDATE1 " +
                                  "from DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE TO_CHAR(TRADE_DATE,'mm/dd/yyyy') IN (TO_CHAR(:v2,'mm/dd/yyyy'),TO_CHAR(:v3,'mm/dd/yyyy'),TO_CHAR(:v4,'mm/dd/yyyy'))" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.OTHER_NAMES,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = date2;
                cmd.Parameters.Add(":v4", OracleType.DateTime).Value = date3;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetBSSummaryByAgentAndDate(string agent, DateTime date1)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.AGENT || ' / ' ||CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "sum(CASE WHEN BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as TOTALAMOUNT, " +
                                  "sum(CASE WHEN BUY_SELL = 'B' THEN "+
                                  "(CASE WHEN SETTLEMENT_STATUS = '0' THEN 0 ELSE AMOUNT END) " +
                                  "ELSE 0 END) as SETTLEAMOUNT " +
                                  "from DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE CLIENT.AGENT = :v1 AND TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B'" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetBSSummaryByAgentAndDate(DateTime date1)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.AGENT || ' / ' ||CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "sum(CASE WHEN BUY_SELL = 'B' THEN AMOUNT ELSE 0 END) as TOTALAMOUNT, " +
                                  "sum(CASE WHEN BUY_SELL = 'B' THEN " +
                                  "(CASE WHEN SETTLEMENT_STATUS = '0' THEN 0 ELSE AMOUNT END) " +
                                  "ELSE 0 END) as SETTLEAMOUNT " +
                                  "from DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'B'" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetSSSummaryByAgentAndDate(string agent, DateTime date1)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.AGENT || ' / ' ||CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "sum(CASE WHEN BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as TOTALAMOUNT, " +
                                  "sum(CASE WHEN BUY_SELL = 'S' THEN " +
                                  "(CASE WHEN SETTLEMENT_STATUS = '0' THEN 0 ELSE AMOUNT END) " +
                                  "ELSE 0 END) as SETTLEAMOUNT " +
                                  "from DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE CLIENT.AGENT = :v1 AND TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S'" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetSSSummaryByAgentAndDate(DateTime date1)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select CLIENT.AGENT || ' / ' ||CLIENT.INITIALS || ' ' || " +
                                  "CLIENT.SURNAME as CLIENTNAME, " +
                                  "sum(CASE WHEN BUY_SELL = 'S' THEN AMOUNT ELSE 0 END) as TOTALAMOUNT, " +
                                  "sum(CASE WHEN BUY_SELL = 'S' THEN " +
                                  "(CASE WHEN SETTLEMENT_STATUS = '0' THEN 0 ELSE AMOUNT END) " +
                                  "ELSE 0 END) as SETTLEAMOUNT " +
                                  "from DAILY_TRANSACTIONS " +
                                  "INNER JOIN CLIENT ON CLIENT.CODE = DAILY_TRANSACTIONS.CLIENT_CODE " +
                                  "WHERE TO_CHAR(SETTLEMENT_DATE,'mm/dd/yyyy') = TO_CHAR(:v2,'mm/dd/yyyy') AND BUY_SELL = 'S'" +
                                  "GROUP BY CLIENT.INITIALS,CLIENT.SURNAME,CLIENT.AGENT";

                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = date1;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static DataTable GetInterestByClientAndDateRange(string clientCode, DateTime fromDate, DateTime toDate)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT INTEREST_DATE as INTERESTDATE, " +
                                  "'PN' as TRXTYPE, " +
                                  "'IN/DELAYED' as REFERENCENUMBER, " +
                                  "(CASE WHEN ACC_POSTED = 2 THEN 'Posted to Loan Account' ELSE 'Int On Delayed Payment' END) as TRANSACTIONPARTICULARS, " +
                                  "INTEREST as INTEREST, " +
                                  "(SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE CLIENT_CODE = :v1 AND LOAN_ACCOUNT.CREATED_DATE < DAILY_INTEREST.INTEREST_DATE + 1) as SETTLEMENTBALANCE " +
                                  "FROM DAILY_INTEREST " +
                                  "WHERE CLIENT_CODE = :v1 AND INTEREST_DATE >= :v2 AND INTEREST_DATE <= :v3 ORDER BY INTEREST_DATE";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientCode;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = fromDate;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = toDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static double GetCumInterest(string clientCode, DateTime date)
        {
            double retVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SUM(INTEREST) FROM DAILY_INTEREST WHERE INTEREST_DATE < :v1 AND CLIENT_CODE = :v2";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double.TryParse(dataReader[0].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static double GetCumLoanAccount(string clientCode, DateTime date)
        {
            double retVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE CREATED_DATE < :v1 AND CLIENT_CODE = :v2";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double.TryParse(dataReader[0].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static double GetAccountBalance(string clientCode, DateTime date)
        {
            double retVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SUM(AMOUNT) FROM DAILY_TRANSACTIONS WHERE TRADE_DATE < :v1 AND CLIENT_CODE = :v2";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double.TryParse(dataReader[0].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static double GetLoanAccountBalance(string clientCode, DateTime date)
        {
            double retVal = 0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT SUM(AMOUNT) FROM LOAN_ACCOUNT WHERE CREATED_DATE < :v1 AND CLIENT_CODE = :v2 AND entry_type in ('PA','RC')";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = date;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = clientCode;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double.TryParse(dataReader[0].ToString(), out retVal);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }
    }
}
